<?php
$page_type = "A";
require_once 'header.php';

if (!empty($_GET["update_success"]))
  $update_success = "&nbsp<font color=\"#CC0000\">{$_GET["update_success"]}</font>";
else
  $update_success = '';

if (!empty($_GET["delete_success"]))
  $delete_success = "&nbsp<font color=\"#CC0000\">{$_GET["delete_success"]}</font>";
else
  $delete_success = '';
  
if (!empty($_GET["reset_success"]))
  $reset_success = "&nbsp<font color=\"#CC0000\">{$_GET["reset_success"]}</font>";
else
  $reset_success = '';

if (!empty($_GET["delete_error"]))
  $delete_error = "&nbsp<font color=\"#CC0000\">{$_GET["delete_error"]}</font>";
else
  $delete_error = '';
  
if (!empty($_GET["reset_error"]))
  $reset_error = "&nbsp<font color=\"#CC0000\">{$_GET["reset_error"]}</font>";
else
  $reset_error = '';
  
echo "<h2>User Management Main</h2>";

// Generate the query section
echo("
  <form method=\"post\" action=\"client.php\">
  Client ID: <input type=\"text\" size=\"20\" maxlength=\"12\" name=\"input_client_id\"> 
  SID: <input type=\"text\" size=\"20\" maxlength=\"10\" name=\"input_sid\"> 
  First Name: <input type=\"text\" size=\"20\" maxlength=\"30\" name=\"input_fname\">
  Last Name: <input type=\"text\" size=\"20\" maxlength=\"30\" name=\"input_lname\"><br><br>
  Street: <input type=\"text\" size=\"20\" maxlength=\"40\" name=\"input_street\"> 
  City: <input type=\"text\" size=\"20\" maxlength=\"32\" name=\"input_city\">
  State: <input type=\"text\" size=\"20\" maxlength=\"32\" name=\"input_state\">
  Zip: <input type=\"text\" size=\"20\" maxlength=\"12\" name=\"input_zip\"><br><br>
  DOB(DD-MON-YY): <input type=\"text\" size=\"20\" maxlength=\"9\" name=\"input_dob\">
  <br> 
  ");

// Dropdown menu for client type
echo("
  <br>
  Client Type:
  <select name=\"input_client_type\">
    <option value=\"\">All</option>
    <option value=\"A\">Administrator</option>
    <option value=\"B\">Student Administrator</option>
    <option value=\"S\">Student</option>
  ");
  
if (!empty($_GET["query_error"]))
  $msg = "<font color=\"#CC0000\"><b>&nbsp{$_GET["query_error"]}</b></font>";
else
  $msg = "";	

$new = "true";
echo("
  </select>
  <input type=\"submit\" value=\"Search\">$msg
  </form><br>

  <form method=\"post\" action=\"client_add.php?new=$new\">
  <input type=\"submit\" value=\"Add client\">$update_success$reset_success$delete_success$delete_error$reset_error<br><br>
  </form>
  ");
  
// Interpret the query requirements
$input_client_id = "";
$input_sid = "";
$input_client_type = "";
$input_fname = "";
$input_lname = "";
$input_dob = "";
$input_street = "";
$input_city = "";
$input_state = "";
$input_zip = "";

if (isset($_POST["input_client_id"])) 
  $input_client_id = $_POST["input_client_id"];
if (isset($_POST["input_sid"])) 
  $input_sid = $_POST["input_sid"];
if (isset($_POST["input_client_type"])) 
  $input_client_type = $_POST["input_client_type"];
if (isset($_POST["input_fname"])) 
  $input_fname = $_POST["input_fname"];
if (isset($_POST["input_lname"])) 
  $input_lname = $_POST["input_lname"];
if (isset($_POST["input_dob"])) 
  $input_dob = $_POST["input_dob"];
if (isset($_POST["input_street"])) 
  $input_street = $_POST["input_street"];
if (isset($_POST["input_city"])) 
  $input_city = $_POST["input_city"];
if (isset($_POST["input_state"])) 
  $input_state = $_POST["input_state"];
if (isset($_POST["input_zip"])) 
  $input_zip = $_POST["input_zip"];

$whereClause = " 1=1 ";

// Add inputted client id to where clause
if (isset($input_client_id) and trim($input_client_id) != "") { 
  $whereClause .= " and Client_ID='$input_client_id'"; 
}
// Add inputted student id to where clause
if (isset($input_sid) and trim($input_sid) != "") { 
  $sid_temp = "%".substr($input_sid, 0, 3)."%";
  $whereClause .= " and sid like '$sid_temp'"; 
}
// Add inputted first name to where clause
if (isset($input_fname) and trim($input_fname) != "") { 
  $fname_temp = "%".substr($input_fname, 1, -1)."%";
  $whereClause .= " and Fname like '$fname_temp'"; 
}
// Add inputted last name to where clause
if (isset($input_lname) and trim($input_lname) != "") { 
  $lname_temp = "%".substr($input_lname, 1, -1)."%";
  $whereClause .= " and Lname like '$lname_temp'"; 
}
// Add inputted dob to where clause
if (isset($input_dob) and trim($input_dob) != "") { 
  $whereClause .= " and DOB like '$input_dob'"; 
}
// Add inputted client type to where clause
if (isset($input_client_type) and $input_client_type != "") { 
  if ($input_client_type == "A")
    $whereClause .= " and AFlag='1' and SFlag = '0'"; 
  else if ($input_client_type == "S")
    $whereClause .= " and SFlag='1' and AFlag = '0'"; 
  else if ($input_client_type == "B")
    $whereClause .= " and AFlag='1' and SFlag='1'";
}
// Add inputted street to where clause
if (isset($input_street) and trim($input_street) != "") { 
  $whereClause .= " and Street like '$input_street'";
}
// Add inputted city to where clause
if (isset($input_city) and trim($input_city) != "") { 
  $whereClause .= " and City like '$input_city'"; 
}
// Add inputted state to where clause
if (isset($input_state) and trim($input_state) != "") { 
  $whereClause .= " and State like '$input_state'"; 
}
// Add inputted zipcode to where clause
if (isset($input_zip) and trim($input_zip) != "") { 
  $whereClause .= " and Zip like '$input_zip'"; 
}

// Form the query statement array and run it
$sql = "select Client_ID, Fname, Lname, AFlag, SFlag, Street, City, State, Zip, Sid, DOB from CLIENT where $whereClause";

$result_array = execute_sql_in_oracle($sql);
$result = $result_array["flag"];
$cursor = $result_array["cursor"];

if (!$result) {
  $query_error = "Client query failed!";
  header("Location:client.php?query_error=$query_error");
}
else {
  // Display the query results
  echo "<table border=1>";
  echo "<tr> <th>Client ID</th> <th>SID</th> <th>Client Type</th> <th>First Name</th> <th>Last Name</th> <th>DOB</th> 
        <th>Address</th> <th>Update</th> <th>Delete</th> <th>Reset Password</th></tr>";

  // Fetch the result from the cursor one by one
  while ($values = oci_fetch_array($cursor)) {
    $client_id = $values[0];
    $fname = $values[1];
    $lname = $values[2];
    $aflag = $values[3];
    $sflag = $values[4];
    $street = $values[5];
    $city = $values[6];
    $state = $values[7];
    $zip = $values[8];
    $sid = $values[9];
	$dob = $values[10];
	
    if ($aflag == '1' and $sflag == '1')
      $client_type = "Student-Admin";
    else if ($aflag == '1' and $sflag == '0')
      $client_type = "Admin";
    else if ($sflag == '1' and $aflag == '0') 
      $client_type = "Student";

    $address = formatAddress($street, $city, $state, $zip);
  
    echo("<tr>" . 
      "<td>$client_id</td> <td>$sid</td> <td>$client_type</td> <td>$fname</td> <td>$lname</td> <td>$dob</td> <td>$address</td>".
      " <td> <A HREF=\"client_update.php?client_id=$client_id\">Update</A> </td> ".
      " <td> <A HREF=\"client_delete.php?client_id=$client_id\">Delete</A> </td> ".
      " <td> <A HREF=\"client_reset_password.php?client_id=$client_id\">Reset Password</A> </td> ".
      "</tr>");
  }
  oci_free_statement($cursor);
  echo "</table>";
}
?>
</body>
</html>